create table Dept
(
	deptId int identity primary key,
	deptName varchar(50) unique not null
)
go

insert into Dept(deptName) values('培训部')
insert into Dept(deptName) values('开发部')
insert into Dept(deptName) values('人事部')
go

create table Employee
(
	empId int identity primary key,
	empName varchar(50) unique not null,
	info varchar(500) not null,
	deptId int not null references Dept(deptId) 
)
go

insert into Employee(empName,info,deptId) values('胡辉煜','培训班老师',1)
insert into Employee(empName,info,deptId) values('陈建','培训班老师',1)

insert into Employee(empName,info,deptId) values('姚文明','开发部组长',2)
insert into Employee(empName,info,deptId) values('谢慧梅','开发部组员',2)
insert into Employee(empName,info,deptId) values('刘备','开发部组员',2)

insert into Employee(empName,info,deptId) values('张三','人事部经理',3)
insert into Employee(empName,info,deptId) values('李四','人事部员工',3)
go

create table Users
(
	userId int identity primary key,
	userName varchar(50) not null,
	password varchar(50) not null,
	nickName varchar(50) not null
)
go

create table UsersInfo
(
	infoId int identity primary key,
	userId int unique references Users(userId),
	imgurl varchar(255) not null,
	address varchar(255) not null
)
go

create table Role
(
	roleId int identity primary key,
	roleName varchar(50) not null
)
go

insert into Role(roleName) values('管理员')
insert into Role(roleName) values('员工')
go

create table Menu
(
	menuId int identity primary key,
	menuName varchar(50) not null,
	menuUrl varchar(255) not null
)
go

insert into Menu(menuName,menuUrl) values('首页','/index.jsp')
insert into Menu(menuName,menuUrl) values('安全退出','/logout.jsp')
insert into Menu(menuName,menuUrl) values('后台管理','/manager.jsp')
insert into Menu(menuName,menuUrl) values('员工管理','/empmanager.jsp')
insert into Menu(menuName,menuUrl) values('部门管理','/deptmanager.jsp')
go

create table RoleMenu
(
	roleId int not null references Role(roleId),
	menuId int not null references Menu(menuId),
	constraint pk_RoleMenu primary key(roleId,menuId)
)
go

create table DTreeMenu
(
	mid int identity primary key,
	name varchar(100) not null,
	url varchar(255) default '' not null,
	target varchar(50) default 'mainFrame' not null,
	parent int null references DTreeMenu(mid) ,
	description varchar(200) not null default '' 
)
go

insert into DTreeMenu(name,url,description) values('首页','/welcome.jsp','登录')
insert into DTreeMenu(name,url,target,description) values('安全退出','/logout.jsp','_top','安全退出')
insert into DTreeMenu(name,description) values('部门管理','部门管理功能列表')
insert into DTreeMenu(name,description) values('权限管理','权限管理功能列表')
insert into DTreeMenu(name,url,parent,description) values('新增部门','/dept/deptadd.jsp','3','新增部门功能')
insert into DTreeMenu(name,url,parent,description) values('显示部门','/dept/showdept.jsp','3','显示部门功能')
insert into DTreeMenu(name,parent,description) values('部门其它','3','部门其它功能')
insert into DTreeMenu(name,url,parent,description) values('修改部门','/dept/deptmodi.jsp','7','修改部门功能')
insert into DTreeMenu(name,url,parent,description) values('显示用户','/admin/showuser.jsp','4','显示用户功能')
insert into DTreeMenu(name,url,parent,description) values('显示角色','/RoleAction/queryAll.do','4','显示角色功能')
go
